*****************************************
** The geography of US banks ** 
** Angeloni, Kasinger and Tantasith **
**       July 2022       **
*****************************************

global location  "ADD HERE"

foreach i of numlist 1990/2021 {

cd "${location}/`i'"

clear all
set more off

********************************************************************************
** Merge sd and call data ** 
********************************************************************************
clear 
use cb_`i'.dta

gen share_lnag = (lnag/lnlsgr)*100
gen share_lnci= (lnci/lnlsgr)*100
gen share_lnci4 = (lnci4/lnlsgr)*100
gen share_lncre = (lncre/lnlsgr)*100
gen share_lnre = (lnre/lnlsgr)*100

egen bank_totasset_rank = rank(-asset)

save cb_`i'_share.dta, replace

clear
use sd_`i'.dta
gen fips = stcntybr
merge m:1 cert using cb_`i'_share.dta
keep if _merge == 3
drop _merge

bysort fips cert: egen depsumbr_sum = sum(depsumbr)
bysort fips cert: keep if _n == 1
drop depsumbr
rename depsumbr_sum depsumbr
drop if depsumbr == 0 | depsumbr == .

sort cert fips
gen obs = _n 

save sd_call_`i'.dta, replace

********************************************************************************
** prepare data for matlab ** 
********************************************************************************

use sd_call_`i'.dta
keep cert fips depsumbr asset
order cert fips depsumbr asset 
save sd_call_`i'_matlab.dta, replace

bysort cert: keep if _n == 1
gen cert_new = _n 
format cert cert_new %12.0f
keep cert cert_new
save cert_new.dta, replace 

clear
use sd_call_`i'_matlab.dta
merge m:1 cert using cert_new.dta
drop _merge
drop cert 
sort cert_new fips
order cert_new fips depsumbr asset 

save sd_call_`i'_matlab.dta, replace
export excel using "sd_call_`i'_matlab.xlsx", replace

}

*****************************
**      after matlab       ** 
*****************************

foreach i of numlist 1990/2021 {

cd "${location}/`i'"

clear
set more off
import excel "asset_dis_`i'.xlsx", sheet("Sheet1") firstrow
rename export1 cert_new
rename export2 asset_dis
gen obs = _n 
save asset_dis_`i'.dta, replace

clear
use sd_call_`i'.dta
merge 1:1 obs using asset_dis_`i'.dta
drop _merge
format cert cert_new %12.0f

*****************************
** check asset distribution ** 
*****************************

bysort cert: egen sum_asset_dis = sum(asset_dis)
gen check = asset - sum_asset_dis
tab check

bysort cert: gen ratio_asset = asset_dis/asset_dis[_n+1]
bysort cert: gen ratio_deposit = depsumbr/depsumbr[_n+1]
gen check2 = ratio_asset - ratio_deposit
tab check2

preserve
keeporder cert fips depsumbr asset_dis ratio_deposit ratio_asset asset sum_asset_dis

export excel using "asset_dis_quality_check_`i'.xlsx", first(var) replace
restore 

*************************************
** collapse data into county-level ** 
*************************************

bysort fips: egen deposit_total = sum(depsumbr)

bysort fips: egen asset_total = sum(asset_dis)
gen ratio = asset_dis/asset_total
bysort fips: egen ratio_sum = sum(ratio)
tab ratio_sum

bysort fips: egen bank_asset_rank = rank(-asset_dis)
bysort fips: egen asset_5large = sum(asset_dis) if bank_asset_rank < 6
bysort fips: gen asset_5large_share = (asset_5large/asset_total)*100

bysort fips: gen asset_first = asset_dis if bank_asset_rank == 1
bysort fips: gen asset_first_share = (asset_first/asset_total)*100
bysort fips: egen asset_3large = sum(asset_dis) if bank_asset_rank < 4 
bysort fips: gen asset_3large_share = (asset_3large/asset_total)*100

bysort fips: egen asset_megabank = sum(asset_dis) if bank_totasset_rank < 6
bysort fips: gen asset_megabank_share = (asset_megabank/asset_total)*100

bysort fips: gen no_bank = _N

bysort fips: egen asset_cb = sum(asset_dis) if cb == 1
bysort fips: gen asset_cb_share = (asset_cb/asset_total)*100

bysort fips: egen asset_bhc = sum(asset_dis) if hctone == 1 | hctmult == 1
bysort fips: gen asset_bhc_share = (asset_bhc/asset_total)*100

bysort fips: egen numemp_c = sum(numemp_dis)

bysort fips: fillmissing asset_first asset_first_share asset_5large asset_5large_share asset_3large asset_3large_share asset_megabank asset_megabank_share asset_cb asset_cb_share asset_bhc asset_bhc_share, with(mean)

local var2 "asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share"
foreach var2 in `var2' { 
replace `var2' = 0 if `var2' == . 
} //for example, a county has only one bank and it is not CB, previous lines of command leave ., which might translate into blank cell in excel.

local varlist rbct1cer rbcrwaj rbc1aaj stbfndr eeffr roe roa roaptx intexpy nperfv nclnlsr astempm depdastr nimy noniiay share_lnag share_lnci share_lnci4 share_lncre share_lnre 

foreach var in `varlist' {

gen d_`var' = 0
replace d_`var' = 1 if `var' == . 
bysort fips: egen dd_`var' = sum(d_`var') 

gen `var'_rat = `var'*ratio
bysort fips: egen `var'_c = sum(`var'_rat) 
}

bysort fips: gen obs2 = _N

foreach var in `varlist' {
replace `var'_c = . if dd_`var' == obs2
}

local varlist2 rbct1cer_c rbcrwaj_c rbc1aaj_c stbfndr_c eeffr_c roe_c roa_c roaptx_c intexpy_c nperfv_c nclnlsr_c astempm_c depdastr_c nimy_c noniiay_c share_lnag_c share_lnci_c share_lnci4_c share_lncre_c share_lnre_c

bysort fips: keep if bank_asset_rank == 1 

order cntynamb stalpbr zipbr asset_total asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share `varlist2', after(fips)

drop cert

save sd_call_county_`i'.dta, replace

}

*****************************
** merge with other data sources ** 
*****************************

set more off

foreach i of numlist 1990/2021 {

if `i' < 2014 {

cd "${location}/`i'"

clear
use mmr_`i'.dta
rename stcntybr fips
merge 1:1 fips using sd_call_county_`i'.dta
drop _merge

drop cntynumb 
format fips %12.0g
order cntynamb stalpbr zipbr mmr asset_total asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share `varlist2', after(fips)

gen year = `i'

replace rbct1cer_c = . //otherwise when exporting to excel, will show as zero.

keeporder fips year cntynamb stalpbr zipbr mmr mmr_desc no_bank ///
deposit_total asset_total asset_first asset_first_share asset_3large asset_3large_share asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share ///
rbct1cer_c rbcrwaj_c rbc1aaj_c stbfndr_c eeffr_c roe_c roa_c roaptx_c intexpy_c nperfv_c nclnlsr_c astempm_c depdastr_c nimy_c noniiay_c  ///
share_lnag_c share_lnci_c share_lnci4_c share_lncre_c share_lnre_c 

save county_data_`i'.dta, replace

}

else if `i' > 2013 {

cd "${location}/`i'"
use mmr_`i'.dta
rename stcntybr fips
merge 1:1 fips using sd_call_county_`i'.dta
keep if _merge == 3
drop _merge

drop cntynumb 
format fips %12.0g
order cntynamb stalpbr zipbr mmr asset_total asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share `varlist2', after(fips)

gen year = `i'

keeporder fips year cntynamb stalpbr zipbr mmr mmr_desc no_bank ///
deposit_total asset_total asset_first asset_first_share asset_3large asset_3large_share asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share ///
rbct1cer_c rbcrwaj_c rbc1aaj_c stbfndr_c eeffr_c roe_c roa_c roaptx_c intexpy_c nperfv_c nclnlsr_c astempm_c depdastr_c nimy_c noniiay_c  ///
share_lnag_c share_lnci_c share_lnci4_c share_lncre_c share_lnre_c 

save county_data_`i'.dta, replace

}
}

*****************************
** Create Panel data ** 
*****************************

clear
cd "${location}/1990"
use county_data_1990.dta

foreach i of numlist 1991/2021 {

append using "${location}/`i'/county_data_`i'.dta"

}

sort fips year

keeporder fips year cntynamb stalpbr zipbr mmr mmr_desc no_bank ///
deposit_total asset_total asset_first asset_first_share asset_3large asset_3large_share asset_5large asset_5large_share asset_cb asset_cb_share asset_bhc asset_bhc_share asset_megabank asset_megabank_share ///
rbct1cer_c rbcrwaj_c rbc1aaj_c stbfndr_c eeffr_c roe_c roa_c roaptx_c intexpy_c nperfv_c nclnlsr_c astempm_c depdastr_c nimy_c noniiay_c  ///
share_lnag_c share_lnci_c share_lnci4_c share_lncre_c share_lnre_c

save "${location}/bankdata_county", replace

*****************************
** Check missing values ** 
*****************************

foreach i of numlist 1990/2021 {
clear
cd "${location}/`i'"
use county_data_`i'.dta
mdesc
}

*****************************
** Merge bank data with economic data ** 
*****************************

use "${location}/bankdata_county", clear

merge 1:1 fips year using "${location}/realdata_public" 

gen asset_gdp = (asset_total/gdp)*100
gen deposit_gdp =(deposit_total/gdp)*100
gen asset_pop = asset_total/pop
gen deposit_pop = deposit_total/pop

keep if _merge == 3 //_merge == 2 are counties not included in the FDIC data
cap drop _merge

drop county_name state 

label var	fips				"FIPS code"
label var	year				"Year"
label var	cntynamb			"County name"
label var	stalpbr				"Abbreviated state name"
label var	zipbr				"Zip code"
label var	mmr					"County classification (1= metro area; 2 = micro area; 3= rural area)"
label var 	mmr_desc 			"County classification_text"

label var	no_bank				"Number of banks"
label var 	asset_5large_share 	"Market share of 5 largest banks"
label var 	asset_cb_share 		"Market share of community banks (%)"
label var	asset_megabank_share "Share of top 5 US banks by assets (%)"
label var	deposit_pop			"Per capita deposits ($ Thou.)"
label var	deposit_gdp			"Deposits to GDP ratio (%)"
label var	asset_pop			"Per capita assets ($ Thou.)"
label var	asset_gdp			"Assets to GDP ratio (%)"

label var	share_lnag_c		"Share of agricultural loans (%)"
label var	share_lnci_c		"Share of commercial and industrial loans (%)"
label var	share_lnci4_c		"Share of commercial and industrial loans below $1 mio (%)"
label var	share_lnre_c		"Share of real estate loans (%)"
label var	noniiay_c			"Non-interest income to asset ratio (%)"

label var	rbct1cer_c			"Common Equity Tier 1 ratio (%)"
label var	rbc1aaj_c 			"Leverage ratio (%)"
label var	eeffr_c				"Efficiency Ratio (%)"
label var	roaptx_c 			"Pretax Return on Assets (%)"
label var	nimy_c				"Net Interest Margin (%)"
label var	nperfv_c			"Non-performing assets ratio (%)"

keeporder fips year cntynamb stalpbr zipbr mmr mmr_desc ///
no_bank asset_5large_share asset_cb_share asset_megabank_share deposit_pop deposit_gdp asset_pop asset_gdp ///
share_lnag_c share_lnci_c share_lnci4_c share_lnre_c noniiay_c ///
rbct1cer_c rbc1aaj_c eeffr_c roaptx_c nimy_c nperfv_c 

save "${location}/geography_usbanks_angeloni_v1.1", replace

*****************************
** Generate no_counties data ** 
*****************************

foreach i of numlist 1990/2021 {

cd "${location}/`i'"

clear
set more off
import excel "asset_dis_`i'.xlsx", sheet("Sheet1") firstrow
rename export1 cert_new
rename export2 asset_dis
gen obs = _n 
save asset_dis_`i'.dta, replace

clear
use sd_call_`i'.dta
merge 1:1 obs using asset_dis_`i'.dta
drop _merge
format cert cert_new %12.0f

gen year = `i'

save no_counties_`i', replace
}

use "${location}/1990/no_counties_1990", clear

foreach i of numlist 1991/2021 {
append using "${location}/`i'/no_counties_`i'"
}

bysort cert year: gen no_counties = _N

save "${location}/no_counties", replace

